﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Xml.Linq;
using System.Text;

namespace Savage.DotNetLibrary.DatabaseLibrary
{
    public class SqlDataWrapper
    {
        public SqlDataWrapper(string connectionString)
        {
            if (String.IsNullOrEmpty(connectionString))
                throw new ArgumentException("Object cannot be initialized with null or empty string for the connection string.", "connectionString");

            _connectionString = connectionString;
        }

        private string _connectionString;

        public SqlConnection CreateConnection()
        {
            return new SqlConnection(_connectionString);
        }

        public SqlCommand CreateCommand(string commandText, CommandType commandType, SqlParameter[] parameters)
        {
            SqlConnection connection = new SqlConnection(_connectionString);

            SqlCommand cmd = new SqlCommand(commandText, connection);
            cmd.CommandType = commandType;
            if (parameters != null)
            {
                cmd.Parameters.AddRange(parameters);
                ConvertParametersToDbNull(cmd);
            }
            
            return cmd;
        }

        public IDataReader ExecuteReader(string commandText, CommandType commandType)
        {
            return ExecuteReader(commandText, commandType, null);
        }

        public IDataReader ExecuteReader(string commandText, CommandType commandType, SqlParameter[] parameters)
        {
            SqlCommand cmd = CreateCommand(commandText, commandType, parameters);
            
            cmd.Connection.Open();
            return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
        }

        public XDocument ExecuteXmlReader(string commandText, CommandType commandType)
        {
            return ExecuteXmlReader(commandText, commandType, null);
        }

        public XDocument ExecuteXmlReader(string commandText, CommandType commandType, SqlParameter[] parameters)
        {
            SqlCommand cmd = CreateCommand(commandText, commandType, parameters);

            using (cmd.Connection)
            {
                cmd.Connection.Open();
                using (System.Xml.XmlReader reader = cmd.ExecuteXmlReader())
                {
                    if (reader.MoveToContent() == System.Xml.XmlNodeType.None)
                        return null;
                    else
                        return XDocument.Load(reader, LoadOptions.None);
                }
            }
        }

        public int ExecuteNonQuery(string commandText, CommandType commandType, SqlParameter[] parameters)
        {
            SqlCommand cmd = CreateCommand(commandText, commandType, parameters);
            try
            {
                cmd.Connection.Open();
                return cmd.ExecuteNonQuery();
            }
            finally
            {
                cmd.Connection.Close();
            }
        }

        public int ExecuteNonQuery(string commandText, CommandType commandType)
        {
            return ExecuteNonQuery(commandText, commandType, null);
        }
        
        public int ExecuteNonQuery(string commandText)
        {
            return ExecuteNonQuery(commandText, CommandType.Text, null);
        }

        public string[] ExecuteReaderToStringArray(string commandText, CommandType commandType, SqlParameter[] parameters, string columnName)
        {
            List<string> list = new List<string>();
            using (IDataReader reader = ExecuteReader(commandText, commandType, parameters))
            {
                while (reader.Read())
                {
                    list.Add(reader[columnName].ToString());
                }
            }
            return list.ToArray();
        }

        public object ExecuteScalar(string commandText, CommandType commandType, SqlParameter[] parameters)
        {
            SqlCommand cmd = CreateCommand(commandText, commandType, parameters);
            ConvertParametersToDbNull(cmd);

            cmd.Connection.Open();
            return cmd.ExecuteScalar();
        }

        private void ConvertParametersToDbNull(SqlCommand cmd)
        {
            foreach (SqlParameter p in cmd.Parameters)
            {
                if (p.Value == null)
                    p.Value = DBNull.Value;
            }
        }

        public void ExecuteTransactionalSqlCommand(SqlTransaction transaction, string commandText)
        {
            SqlCommand command = new SqlCommand(commandText, transaction.Connection);
            if (transaction != null)
            {
                command.Transaction = transaction;
            }

            try
            {
                if (transaction.Connection.State != System.Data.ConnectionState.Open)
                {
                    transaction.Connection.Open();
                }
                command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new Exception("Exception in ExecuteSqlCommand.\r\n\rSqlCommand.CommandType: " + command.CommandType.ToString() + "\r\n\rSqlCommand.CommandText: " + command.CommandText, ex);
            }
            finally
            {
                if (transaction == null)
                {
                    transaction.Connection.Close();
                }
            }
        }
    }
}
